﻿CREATE PROCEDURE [maint].[CheckDB]
@DbName [sysname], @CheckSchedule BIT=1, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int;
	SET @RC = 0;

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + '''';
			PRINT N'	@CheckSchedule = N''' + ISNULL(CAST(@CheckSchedule AS nvarchar),N'NULL') + '''';
		END

	DECLARE @ActionToken CHAR(1);
	SET @ActionToken = 'C';
	
	DECLARE @CheckDbRequired bit;
	SET @CheckDbRequired = 1;
	
	IF (@CheckSchedule = 1)
		EXEC @RC = maint.GetCheckDbScheduleStatus @DbName, @CheckDbRequired OUTPUT

	IF (@CheckDbRequired = 0)
	BEGIN
		--check previous status
		EXEC @RC = maint.CheckErrorStatus @DbName, @ActionToken;
		
		GOTO EXIT_PROC
	END
		
	PRINT REPLICATE('-',100);
	PRINT N'CHECKING CONSISTANCY OF DATABASE ' + UPPER(@DbName);

	DECLARE @CheckDbStmnt nvarchar(255)
	SET @CheckDbStmnt = N'DBCC CHECKDB(''' + @DbName + ''',NOINDEX)'
		+ (CASE @OutputDiagnostics
			WHEN 0 THEN 'WITH NO_INFOMSGS'
			ELSE N''
		END)
	
	BEGIN TRY
		EXEC dbo.sp_executesql @CheckDbStmnt
		
		--clear error status flag
		EXEC maint.SetErrorStatus @DbName, @ActionToken, 0;
	END TRY
	BEGIN CATCH
		SET @RC = ERROR_NUMBER();
		
		DECLARE @ErrorMessage NVARCHAR(4000);
		SET @ErrorMessage = 'Consistancy check for database %s failed with the following error: ' + ERROR_MESSAGE();
		
		--set error status flag	
		EXEC maint.SetErrorStatus @DbName, @ActionToken, 1;
		
		RAISERROR(@ErrorMessage,16,1,@DbName);
		
		GOTO EXIT_PROC
	END CATCH

	GOTO EXIT_PROC

EXIT_PROC:
	RETURN(@RC)
END